<?php

require_once('../includes/DB.class.php');
require_once('../includes/Verifier.class.php');
require_once('../includes/Filter.class.php');
require_once('../includes/SimpleTableExporter.class.php');

ini_set("max_execution_time",0);
ini_set( "memory_limit" , "128M");
$filename = '总表搜索'.'_'.date('YmdHis').'.xls';
$exp = new SimpleTableExporter($filename);

if(isset($_GET['materialCode'])){
	$_GET['materialCode'] = Filter::forDBInsertion(trim($_GET['materialCode']));
}

if(isset($_GET['certificationNumber'])){
	$_GET['certificationNumber'] = Filter::forDBInsertion(trim($_GET['certificationNumber']));
}

if(isset($_GET['facheNumber'])){
	$_GET['facheNumber'] = Filter::forDBInsertion(trim($_GET['facheNumber']));
}

if(isset($_GET['size1From'])){
	$_GET['size1From'] = trim($_GET['size1From']);
}
if(isset($_GET['size1To'])){
	$_GET['size1To'] = trim($_GET['size1To']);
}
if(isset($_GET['size2From'])){
	$_GET['size2From'] = trim($_GET['size2From']);
}
if(isset($_GET['size2To'])){
	$_GET['size2To'] = trim($_GET['size2To']);
}
if(isset($_GET['size3From'])){
	$_GET['size3From'] = trim($_GET['size3From']);
}
if(isset($_GET['size3To'])){
	$_GET['size3To'] = trim($_GET['size3To']);
}
if(isset($_GET['uploadTimeFrom'])){
	$_GET['uploadTimeFrom'] = trim($_GET['uploadTimeFrom']);
}
if(isset($_GET['uploadTimeTo'])){
	$_GET['uploadTimeTo'] = trim($_GET['uploadTimeTo']);
}
if(isset($_GET['shipmentDateFrom'])){
	$_GET['shipmentDateFrom'] = trim($_GET['shipmentDateFrom']);
}

if(isset($_GET['shipmentDateTo'])){
	$_GET['shipmentDateTo'] = trim($_GET['shipmentDateTo']);
}

if(isset($_GET['certificationCodification'])){
	$_GET['certificationCodification'] = trim($_GET['certificationCodification']);
}

if(isset($_GET['sequenceNumber'])){
	$_GET['sequenceNumber'] = Filter::forDBInsertion(trim($_GET['sequenceNumber']));
}
if(isset($_GET['manufactory'])){
	$_GET['manufactory'] = Filter::forDBInsertion(trim($_GET['manufactory']));
}

if(isset($_GET['authenticationMark'])){
	$_GET['authenticationMark'] = Filter::forDBInsertion(trim($_GET['authenticationMark']));
}

if(isset($_GET['steelNumber'])){
	$_GET['steelNumber'] = Filter::forDBInsertion(trim($_GET['steelNumber']));
}

if(isset($_GET['orderNumber'])){
	$_GET['orderNumber'] = Filter::forDBInsertion(trim($_GET['orderNumber']));
}

if(isset($_GET['orderSubitemNumber'])){
	$_GET['orderSubitemNumber'] = trim($_GET['orderSubitemNumber']);
}

if(isset($_GET['unitPrice'])){
	$_GET['unitPrice'] = trim($_GET['unitPrice']);
}

if(isset($_GET['materialNumber'])){
	$_GET['materialNumber'] = Filter::forDBInsertion(trim($_GET['materialNumber']));
}

if(isset($_GET['consignmentBatch'])){
	$_GET['consignmentBatch'] = Filter::forDBInsertion(trim($_GET['consignmentBatch']));
}

if(isset($_GET['filename'])){
	$_GET['filename'] = Filter::forDBInsertion(trim($_GET['filename']));
}
if(isset($_GET['remark'])){
	$_GET['remark'] = Filter::forDBInsertion(trim($_GET['remark']));
}

if(isset($_GET['sampleBatch'])){
	$_GET['sampleBatch'] = Filter::forDBInsertion(trim($_GET['sampleBatch']));
}
if(isset($_GET['purchaseNumber'])){
	$_GET['purchaseNumber'] = Filter::forDBInsertion(trim($_GET['purchaseNumber']));
}

if(isset($_GET['month'])){
	$_GET['month'] = Filter::forDBInsertion(trim($_GET['month']));
}
if(isset($_GET['vehicleNumber'])){
	$_GET['vehicleNumber'] = Filter::forDBInsertion(trim($_GET['vehicleNumber']));
}
if(isset($_GET['consignee'])){
	$_GET['consignee'] = Filter::forDBInsertion(trim($_GET['consignee']));
}

$mainTable = true;
$main3Table = true;

if( !empty($_GET['materialCode']) || !empty($_GET['sequenceNumber']) || !empty($_GET['manufactory']) ||
	!empty($_GET['authenticationMark']) || !empty($_GET['steelNumber']) || !empty($_GET['size1From']) ||
	!empty($_GET['size2From']) || !empty($_GET['size3From']) || !empty($_GET['size1To']) || !empty($_GET['size2To']) || !empty($_GET['size3To']) || !empty($_GET['unitWeight']) || !empty($_GET['orderNumber'])
	|| !empty($_GET['orderSubitemNumber']) || !empty($_GET['unitPrice']) || !empty($_GET['purchaseNumber'])
	|| !empty($_GET['remark']) || !empty($_GET['uploadTimeFrom']) || !empty($_GET['uploadTimeTo']) || !empty($_GET['filename'])){
	$mainTable = false;
}else{
	$main3Table = false;
}

if($mainTable){
	$mainWheres = array();
	
	if(!empty($_GET['certificationNumber'])){
		array_push($mainWheres, "certificationNumber like '%{$_GET['certificationNumber']}%'");
	}
	
	if(!empty($_GET['consignmentBatch'])){
		array_push($mainWheres, "consignmentBatch like '%{$_GET['consignmentBatch']}%'");
	}
	
	if(!empty($_GET['certificationCodification'])){
		array_push($mainWheres, "certificationCodification like '%{$_GET['certificationCodification']}%'");
	}
	
	if(!empty($_GET['facheNumber'])){
		array_push($mainWheres, "facheNumber like '%{$_GET['facheNumber']}%'");
	}
	
	if(!empty($_GET['sampleBatch'])){
		array_push($mainWheres, "sampleBatch like '%{$_GET['sampleBatch']}%'");
	}
	
	if(!empty($_GET['month'])){
		array_push($mainWheres, "month like '%{$_GET['month']}%'");
	}
	
	if(!empty($_GET['vehicleNumber'])){
		array_push($mainWheres, "vehicleNumber like '%{$_GET['vehicleNumber']}%'");
	}
	
	if(!empty($_GET['consignee'])){
		array_push($mainWheres, "consignee like '%{$_GET['consignee']}%'");
	}
	
	if(!empty($_GET['shipmentDateFrom']) || Verifier::isTIME($_GET['shipmentDateFrom'])){
		$time = date('Y/m/d', strtotime($_GET['shipmentDateFrom']));
		array_push($mainWheres, "shipmentDate >= '$time'");
	}

	if(!empty($_GET['shipmentDateTo']) || Verifier::isTIME($_GET['shipmentDateTo'])){
		$time = date('Y/m/d', strtotime($_GET['shipmentDateTo']));
		array_push($mainWheres, "shipmentDate <= '$time'");
	}
	$whereStr = join(' and ', $mainWheres);
	
	if(count($mainWheres)==0){
		$query = "select *,sum(count) as mainCount from little_main
				group by orderNumber,orderSubitemNumber
				order by certificationNumber, size1, size2, size3";
	}else{
		$query = "select *,sum(count) as mainCount from little_main where $whereStr
				group by orderNumber,orderSubitemNumber
				order by certificationNumber, size1, size2, size3";
	}
}


if($main3Table){
	
		$main3Wheres = array();
		
		if(!empty($_GET['materialCode'])){
			array_push($main3Wheres, "materialCode like '%{$_GET['materialCode']}%'");
		}
	
		if(!empty($_GET['size1From']) && Verifier::isNUMBER($_GET['size1From'])){
			$size1From = doubleval($_GET['size1From']);
			array_push($main3Wheres, "size1 >= {$size1From}");
		}
		
		if(!empty($_GET['size1To']) && Verifier::isNUMBER($_GET['size1To'])){
			$size1To = doubleval($_GET['size1To']);
			array_push($main3Wheres, "size1 <= {$size1To}");
		}
		
		if(!empty($_GET['size2From']) && Verifier::isNUMBER($_GET['size2From'])){
			$size2From = doubleval($_GET['size2From']);
			array_push($main3Wheres, "size2 >= {$size2From}");
		}
		
		if(!empty($_GET['size2To']) && Verifier::isNUMBER($_GET['size2To'])){
			$size2To = doubleval($_GET['size2To']);
			array_push($main3Wheres, "size2 <= {$size2To}");
		}
		
		if(!empty($_GET['size3From']) && Verifier::isNUMBER($_GET['size3From'])){
			$size3From = doubleval($_GET['size3From']);
			array_push($main3Wheres, "size3 >= {$size3From}");
		}
		
		if(!empty($_GET['size3To']) && Verifier::isNUMBER($_GET['size3To'])){
			$size3To = doubleval($_GET['size3To']);
			array_push($main3Wheres, "size3 <= {$size3To}");
		}
	
		if(!empty($_GET['unitPrice'])){
			array_push($main3Wheres, "unitPrice = '{$_GET['unitPrice']}'");
		}
		
		if(!empty($_GET['purchaseNumber'])){
			array_push($main3Wheres, "purchaseNumber like '%{$_GET['purchaseNumber']}%'");
		}
		
		if(!empty($_GET['materialNumber'])){
			array_push($main3Wheres, "materialNumber like '%{$_GET['materialNumber']}%'");
		}
		
		if(!empty($_GET['sequenceNumber'])){
			array_push($main3Wheres, "sequenceNumber like '%{$_GET['sequenceNumber']}%'");
		}
		
		if(!empty($_GET['manufactory'])){
			array_push($main3Wheres, "manufactory like '%{$_GET['manufactory']}%'");
		}
		if(!empty($_GET['authenticationMark'])){
			array_push($main3Wheres, "authenticationMark like '%{$_GET['authenticationMark']}%'");
		}
		
		if(!empty($_GET['steelNumber'])){
			array_push($main3Wheres, "steelNumber like '%{$_GET['steelNumber']}%'");
		}
		
		if(!empty($_GET['remark'])){
			array_push($main3Wheres, "remark like '%{$_GET['remark']}%'");
		}
		
		if(!empty($_GET['orderNumber'])){
			array_push($main3Wheres, "orderNumber like '%{$_GET['orderNumber']}%'");
		}
		
		if(!empty($_GET['orderSubitemNumber'])){
			array_push($main3Wheres, "orderSubitemNumber = '{$_GET['orderSubitemNumber']}'");
		}
		
		if(!empty($_GET['uploadTimeFrom']) && Verifier::isTIME($_GET['uploadTimeFrom'])){
			$time = date('Y/m/d H:i:s', strtotime($_GET['uploadTimeFrom']));
			array_push($main3Wheres, "uploadTime >= '$time'");
		}
		
		if(!empty($_GET['uploadTimeTo']) && Verifier::isTIME($_GET['uploadTimeTo'])){
			$time = date('Y/m/d H:i:s', strtotime($_GET['uploadTimeTo']));
			array_push($main3Wheres, "uploadTime <= '$time'");
		}
		
		if(!empty($_GET['filename'])){
			array_push($main3Wheres, "filename like '%{$_GET['filename']}%'");
		}
		$main3WheresStr = join(' and ', $main3Wheres);
		
		if(count($main3Wheres)==0){
			$query = "select *,sum(count) as main3Count from little_main3
					group by orderNumber,orderSubitemNumber
					order by materialCode, size1, size2, size3";
		}else{
			$query = "select *,sum(count) as main3Count from little_main3 where $main3WheresStr
					group by orderNumber,orderSubitemNumber
					order by materialCode, size1, size2, size3";
		}
		
}

$_materialCode = array();
$_main3Count = array();
$_main3SumCount = array();
$_main3AllocatedCount = array();
$_mainAllocatedCount = array();
$_mainCount = array();
$_mainSumCount = array();
$_mainCertificationCount = array();
$_sequenceNumber = array();
$_manufactory = array();
$_authenticationMark = array();
$_steelNumber =  array();
$_size1 = array();
$_size2 = array();
$_size3 = array();
$_unitWeight = array();
$_orderNumber = array();
$_orderSubitemNumber = array();
$_unitPrice = array();
$_purchaseNumber = array();
$_remark = array();
$_certificationNumber = array();
$_sampleBatch = array();
$_certificationCodification = array();
$_facheNumber = array();
$_materialNumber = array();
$_consignmentBatch = array();
$_shipmentDate = array();
$_month = array();
$_vehicleNumber = array();
$_consignee = array();
$_filename = array();
$_uploadTime = array();

$result = DB::query($query);
if($result == false){
	return;
}

while(($row = $result->fetch_assoc())!=null){
	$main3Sql = "select * from
			(
			select *,sum(`count`) as main3Count from little_main3
			where orderNumber = '{$row['orderNumber']}' and orderSubitemNumber = '{$row['orderSubitemNumber']}'
			group by materialCode
			order by main3Count
			)as main3Table
			 
			left outer join
			
			(
			select orderNumber, orderSubitemNumber,sum(`count`) as main3SumCount from little_main3
			group by orderNumber, orderSubitemNumber
			)as main3SumTable
			using (orderNumber, orderSubitemNumber)
			";
	$main3Result = DB::query($main3Sql);
	$materialCode = array();
	$main3Count = array();
	$main3SumCount = array();
	$sequenceNumber = array();
 	$manufactory = array();
	$authenticationMark = array();
	$steelNumber =  array();
	$size1 = array();
 	$size2 = array();
	$size3 = array();
	$unitWeight = array();
	$orderNumber = array();
	$orderSubitemNumber = array();
	$unitPrice = array();
	$purchaseNumber = array();
	$remark = array();
	$filename = array();
	$uploadTime = array();
	while(($main3Row = $main3Result->fetch_assoc())!=null){
		array_push($materialCode,$main3Row['materialCode']);
		array_push($main3Count, $main3Row['main3Count']);
		array_push($main3SumCount, $main3Row['main3SumCount']);
		array_push($sequenceNumber, $main3Row['sequenceNumber']);
		array_push($authenticationMark, $main3Row['authenticationMark']);
		array_push($manufactory, $main3Row['manufactory']);
		array_push($steelNumber, $main3Row['steelNumber']);
		array_push($size1, $main3Row['size1']);
		array_push($size2, $main3Row['size2']);
		array_push($size3, $main3Row['size3']);
		array_push($unitWeight, $main3Row['unitWeight']);
		array_push($orderNumber, $main3Row['orderNumber']);
		array_push($orderSubitemNumber, $main3Row['orderSubitemNumber']);
		array_push($unitPrice, $main3Row['unitPrice']);
		array_push($purchaseNumber, $main3Row['purchaseNumber']);
		array_push($remark, $main3Row['remark']);
		array_push($filename, $main3Row['filename']);
		array_push($uploadTime, $main3Row['uploadTime']);
	}
	
	$mainSql = "select * from
			(
			select *,sum(`count`) as mainCount from little_main
			where orderNumber = '{$row['orderNumber']}' and orderSubitemNumber = '{$row['orderSubitemNumber']}'
			group by certificationNumber, sampleBatch
			order by mainCount
			)as mainTable
			
			left outer join
			
			(
			select orderNumber, orderSubitemNumber,sum(`count`) as mainSumCount from little_main
			group by orderNumber, orderSubitemNumber
			)as mainSumTable
			using (orderNumber, orderSubitemNumber)
			
			left outer join
			
			(
			select certificationNumber,orderNumber, orderSubitemNumber, sum(`count`) as mainCertificationCount
			from little_main
			group by certificationNumber
			)as mainCertificationTable
			using (certificationNumber, orderNumber, orderSubitemNumber)
			";
	$mainResult = DB::query($mainSql);
	$certificationNumber = array();
	$sampleBatch = array();
	$certificationCodification = array();
	$facheNumber = array();
	$materialNumber = array();
	$consignmentBatch = array();
	$shipmentDate = array();
	$month = array();
	$vehicleNumber = array();
	$consignee = array();
	$mainCount = array();
	$mainSumCount = array();
	$mainCertificationCount = array();
	while(($mainRow = $mainResult->fetch_assoc())!=null){
		array_push($certificationNumber, $mainRow['certificationNumber']);
		array_push($sampleBatch, $mainRow['sampleBatch']);
		array_push($certificationCodification, $mainRow['certificationCodification']);
		array_push($facheNumber, $mainRow['facheNumber']);
		array_push($materialNumber, $mainRow['materialNumber']);
		array_push($consignmentBatch, $mainRow['consignmentBatch']);
		array_push($shipmentDate, $mainRow['shipmentDate']);
		array_push($month, $mainRow['month']);
		array_push($vehicleNumber, $mainRow['vehicleNumber']);
		array_push($consignee, $mainRow['consignee']);
		array_push($mainCount, $mainRow['mainCount']);
		array_push($mainSumCount,$mainRow['mainSumCount']);
		array_push($mainCertificationCount,$mainRow['mainCertificationCount']);
	}

	//是否需要对mainCountTemp重新赋值
	$resetMainCountTemp = true;
	
	//分配原则算法
	for ($main3Key = count($main3Count)-1;$main3Key >=0 ;$main3Key--){
		$main3CountTemp = $main3Count[$main3Key];
		$main3AllocatedCountTemp = 0;
		$loopCount = 0;
		for($mainKey = count($mainCount)-1;$mainKey >=0 ;$mainKey--,$loopCount++) {
			if($resetMainCountTemp)
				$mainCountTemp = $mainCount[$mainKey];
			array_push($_mainSumCount, $mainSumCount[$mainKey]);
			array_push($_mainCount,$mainCount[$mainKey]);
			array_push($_mainCertificationCount, $mainCertificationCount[$mainKey]);
			array_push($_certificationNumber,$certificationNumber[$mainKey]);
			array_push($_sampleBatch,$sampleBatch[$mainKey]);
			array_push($_certificationCodification,$certificationCodification[$mainKey]);
			array_push($_facheNumber,$facheNumber[$mainKey]);
			array_push($_materialNumber,$materialNumber[$mainKey]);
			array_push($_consignmentBatch,$consignmentBatch[$mainKey]);
			array_push($_shipmentDate,$shipmentDate[$mainKey]);
			array_push($_month,$month[$mainKey]);
			array_push($_vehicleNumber,$vehicleNumber[$mainKey]);
			array_push($_consignee,$consignee[$mainKey]);
			array_push($_materialCode,$materialCode[$main3Key]);
			array_push($_main3SumCount,$main3SumCount[$main3Key]);
			array_push($_main3Count,$main3Count[$main3Key]);
			array_push($_sequenceNumber,$sequenceNumber[$main3Key]);
			array_push($_manufactory,$manufactory[$main3Key]);
			array_push($_authenticationMark,$authenticationMark[$main3Key]);
			array_push($_steelNumber,$steelNumber[$main3Key]);
			array_push($_size1,$size1[$main3Key]);
			array_push($_size2,$size2[$main3Key]);
			array_push($_size3,$size3[$main3Key]);
			array_push($_unitWeight,$unitWeight[$main3Key]);
			array_push($_orderNumber,$orderNumber[$main3Key]);
			array_push($_orderSubitemNumber,$orderSubitemNumber[$main3Key]);
			array_push($_unitPrice,$unitPrice[$main3Key]);
			array_push($_purchaseNumber,$purchaseNumber[$main3Key]);
			array_push($_remark,$remark[$main3Key]);
			array_push($_filename,$filename[$main3Key]);
			array_push($_uploadTime,$uploadTime[$main3Key]);
			if($main3CountTemp<$mainCountTemp){
				array_push($_mainAllocatedCount,$main3CountTemp);
				array_push($_main3AllocatedCount,$main3CountTemp);
				$mainCountTemp = $mainCountTemp - $main3CountTemp;
				array_pop($main3Count);
				$resetMainCountTemp = false;
				continue 2;
			}else if($main3CountTemp>$mainCountTemp){
				array_push($_mainAllocatedCount,$mainCountTemp);
				array_push($_main3AllocatedCount,$mainCountTemp);
				$main3CountTemp = $main3CountTemp - $mainCountTemp;
				if($loopCount>0)
					$main3AllocatedCountTemp = $main3AllocatedCountTemp + $mainCountTemp;
				else 
					$main3AllocatedCountTemp = $mainCountTemp;
				array_pop($mainCount);
				$resetMainCountTemp = true;
				continue;
			}else if($main3CountTemp==$mainCountTemp){
				array_push($_mainAllocatedCount,$mainCountTemp);
				array_push($_main3AllocatedCount,$mainCountTemp);
				array_pop($main3Count);
				array_pop($mainCount);
				$resetMainCountTemp = true;
				continue 2;
			}
		}
		if(empty($mainCount)){
			array_push($_mainAllocatedCount, 0);
			array_push($_mainSumCount, 0);
			array_push($_mainCount,0);
			array_push($_mainCertificationCount,0);
			array_push($_certificationNumber,'');
			array_push($_sampleBatch,'');
			array_push($_certificationCodification,'');
			array_push($_facheNumber,'');
			array_push($_materialNumber,'');
			array_push($_consignmentBatch,'');
			array_push($_shipmentDate,'');
			array_push($_month,'');
			array_push($_vehicleNumber,'');
			array_push($_consignee,'');
			array_push($_materialCode,$materialCode[$main3Key]);
			array_push($_main3SumCount,$main3SumCount[$main3Key]);
			array_push($_main3Count,$main3Count[$main3Key]);
			array_push($_main3AllocatedCount,$main3Count[$main3Key] - $main3AllocatedCountTemp);
			array_push($_sequenceNumber,$sequenceNumber[$main3Key]);
			array_push($_manufactory,$manufactory[$main3Key]);
			array_push($_authenticationMark,$authenticationMark[$main3Key]);
			array_push($_steelNumber,$steelNumber[$main3Key]);
			array_push($_size1,$size1[$main3Key]);
			array_push($_size2,$size2[$main3Key]);
			array_push($_size3,$size3[$main3Key]);
			array_push($_unitWeight,$unitWeight[$main3Key]);
			array_push($_orderNumber,$orderNumber[$main3Key]);
			array_push($_orderSubitemNumber,$orderSubitemNumber[$main3Key]);
			array_push($_unitPrice,$unitPrice[$main3Key]);
			array_push($_purchaseNumber,$purchaseNumber[$main3Key]);
			array_push($_remark,$remark[$main3Key]);
			array_push($_filename,$filename[$main3Key]);
			array_push($_uploadTime,$uploadTime[$main3Key]);
		}
	}
}
$data = array($_materialCode,$_certificationNumber,$_certificationCodification, $_facheNumber, $_sequenceNumber, 
				  $_manufactory, $_authenticationMark, $_steelNumber, $_size1, $_size2, $_size3, $_unitPrice,
				  $_orderNumber, $_orderSubitemNumber, $_unitPrice,$_materialNumber, 
				  $_consignmentBatch, $_sampleBatch,$_purchaseNumber,$_shipmentDate,
				  $_month,$_vehicleNumber, $_consignee, $_remark, $_filename, $_uploadTime,
				  $_main3AllocatedCount, $_main3Count, $_main3SumCount, $_mainAllocatedCount,
				  $_mainCount, $_mainCertificationCount, $_mainSumCount);
$exp->insertMainTableData($data);
$exp->export();
?>